﻿//
// (C) Copyright 2009 Irantha Suwandarathna (iranthas@hotmail.com)
// All rights reserved.
//

/********************************************************
 * ADO.NET 2.0 Data Provider for SQLite Version 3.X
 * Written by Robert Simpson (robert@blackcastlesoft.com)
 * 
 ********************************************************/

using System;
using System.Data;
using System.Data.Common;
using System.Collections.Generic;
using System.Globalization;
using System.IO;
using System.Text;
using EffiProz.Core;
using EffiProz.Core.Lib;
using EffiProz.Core.DataTypes;
using EffiProz.Core.Results;
using EffiProz.Core.Navigators;
using EffiProz.Core.Errors;

namespace System.Data.EffiProz
{
    internal sealed class EfzStatement : IDisposable
    {
      
        /// <summary>
        /// The command text of this SQL statement
        /// </summary>
        string _sqlStatement;
       
       
        /// <summary>
        /// Command this statement belongs to (if any)
        /// </summary>
        EfzCommand _command;

        /**
         * Description of result set metadata. 
         */
        ResultMetaData _rsmdDescriptor;

        /** Description of parameter metadata. */
        ResultMetaData _pmdDescriptor;


        Result _resultOut;

        /** The currently existing generated key Result */
        Result _generatedResult = null;

        /** Any error returned from a batch execute. */
        Result _errorResult = null;

        List<EfzException> _sqlWarnings = null;

        /**
         * The id with which this object's corresponding
         * {@link org.hsqldb.CompiledStatement CompiledStatement}
         * object is registered in the engine's
         * {@link org.hsqldb.CompiledStatementManager CompiledStatementManager}
         * object.
         */
        long _statementID = -1;

        bool _isRowCount;

        /** The Names of the parameters. */
        string[] _parameterNames;

        /** The SQL types of the parameters. */
        SqlType[] _parameterTypes;

        /** The (IN, IN OUT, or OUT) modes of parameters */
        byte[] _parameterModes;

        /** Flags for bound variables. */
        bool[] _parameterSet;

        /** The parameter values for the next non-batch execution. */
        Object[] _parameterValues;

        /** Lengths for streams. */
        long[] _streamLengths;

        int queryTimeout = 0;
        int resultSetType = ResultConstants.TYPE_FORWARD_ONLY;
        int resultSetConcurrency = ResultConstants.CONCUR_READ_ONLY;
        int resultSetHoldability = ResultConstants.CLOSE_CURSORS_AT_COMMIT;    
        int statementReturnType = StatementTypes.RETURN_ANY;

        public Result GeneratedResult
        {
            get { return _generatedResult; }
        }

        public bool IsPrepared
        {
            get;
            set;
        }

      
        internal EfzStatement(EfzCommand command, string strCommand,bool prepare)
        {
            _command = command;
            if (command.CommandType == CommandType.StoredProcedure)
            {
                _sqlStatement = BuildStoredProcedureSql(strCommand, command.Connection);
                prepare = true;
            }
            else
                _sqlStatement = strCommand;

            IsPrepared = false;
           
            if (prepare)
            {
                PrepareStatement();
                IsPrepared = true;
            }
        }

        internal Result Execute(int maxrows, int fetchsize)
        {
            Result resultIn;
            Result _resultOut = Result.newExecuteDirectRequest();

            try
            {
                _resultOut.setPrepareOrExecuteProperties(_sqlStatement, maxrows, fetchsize,statementReturnType,
                  queryTimeout,resultSetType,resultSetConcurrency,resultSetConcurrency,
                  _command.FetchGeneratedResults ? ResultConstants.RETURN_GENERATED_KEYS : ResultConstants.RETURN_NO_GENERATED_KEYS,
                 null,null);
                resultIn = _command.Connection.InnerConnection.SessionProxy.execute(_resultOut);
                PerformPostExecute(resultIn);
            }
            catch (CoreException e)
            {
                throw EfzException.GetException(e);
            }
            finally
            {
                _resultOut.clearLobResults();
                
            }

            if (resultIn.isError())
            {
                EfzException.ThrowError(resultIn);
            }

            return resultIn;
        }

        internal Result Execute(EfzParameterCollection parameters , int maxrows, int fetchsize)
        {
            Result resultIn;

            BindParameters(_command.Parameters);
            PerformPreExecute();

            try
            {
                _resultOut.setPreparedExecuteProperties(_parameterValues, maxrows, fetchsize);
                resultIn = _command.Connection.InnerConnection.SessionProxy.execute(_resultOut);
                PerformPostExecute(resultIn);
            }
            catch (CoreException e)
            {
                throw EfzException.GetException(e);
            }
            finally
            {
                _resultOut.clearLobResults();
            }

            if (resultIn.isError())
            {
                EfzException.ThrowError(resultIn);
            }

            return resultIn;
        }

        internal void BindParameters(EfzParameterCollection paramCollection)
        {
            DbParameter para;
            string paraName;
            for (int i = 0; i < _parameterNames.Length; i++ )
            {
                paraName = _parameterNames[i];
                para = _command.Parameters[paraName];

                if (para.Direction != ParameterDirection.Input)
                    throw new NotSupportedException(para.Direction.ToString());

                if(para == null)
                    throw new ArgumentException("Missing Parameter "+paraName);

                SetParameter(i, para.Value);               
                _streamLengths[i] = para.Size;
            }

        }

      /**
       * processes chained warnings and any generated columns result set
       */
        void PerformPostExecute(Result rResult)
        {
            _generatedResult = null;

            if (rResult == null)
            {
                return;
            }

            Result current = rResult;

            while (current.getChainedResult() != null)
            {
                current = current.getUnlinkChainedResult();

                if (current.getType() == ResultConstants.WARNING)
                {
                    EfzException w = EfzException.SqlWarning(current);

                    if (_sqlWarnings == null)
                        _sqlWarnings = new List<EfzException>();

                    _sqlWarnings.Add(w);

                }
                else if (current.getType() == ResultConstants.ERROR)
                {
                    _errorResult = current;
                }
                else if (current.getType() == ResultConstants.DATA)
                {
                    _generatedResult = current;
                }
            }

            //connection.setWarnings(rootWarning);
        }


        private void PerformPreExecute()
        {
            for (int i = 0; i < _parameterValues.Length; i++)
            {
                Object value = _parameterValues[i];

                if (value == null)
                    continue;

                SessionInterface session = _command.Connection.InnerConnection.SessionProxy;

                if (_parameterTypes[i].typeCode == Types.SQL_BLOB)
                {
                    long id;
                    BlobDataID blob = null;

                    if (value is byte[])
                    {
                        long length = ((byte[])value).Length;
                        blob = session.createBlob(length);
                        id = blob.getId();

                        MemoryStream stream = new MemoryStream((byte[])value);

                        ResultLob resultLob = ResultLob.newLobCreateBlobRequest(
                            session.getId(), id, stream, length);

                        session.allocateResultLob(resultLob, null);
                        _resultOut.addLobResult(resultLob);
                    }
                    else if (value is Stream)
                    {
                        long length = _streamLengths[i];

                        blob = session.createBlob(length);
                        id = blob.getId();

                        Stream stream = (Stream)value;

                        ResultLob resultLob = ResultLob.newLobCreateBlobRequest(
                            session.getId(), id, stream, length);

                        session.allocateResultLob(resultLob, null);
                        _resultOut.addLobResult(resultLob);
                    }
                    else
                        throw new InvalidCastException();

                    _parameterValues[i] = blob;
                }
                else if (_parameterTypes[i].typeCode == Types.SQL_CLOB)
                {
                    long id;
                    ClobDataID clob = null;
                    if (value is char[])
                    {
                        long length = ((char[])value).Length;
                        TextReader reader = new StringReader(new String(((char[])value)));

                        clob = session.createClob(length);
                        id = clob.getId();

                        ResultLob resultLob = ResultLob.newLobCreateClobRequest(
                            session.getId(), id, reader, length);

                        session.allocateResultLob(resultLob, null);
                        _resultOut.addLobResult(resultLob);
                    }
                    else if (value is TextReader)
                    {
                        long length = _streamLengths[i];

                        clob = session.createClob(length);
                        id = clob.getId();

                        TextReader reader = (TextReader)value;
                        ResultLob resultLob = ResultLob.newLobCreateClobRequest(
                            session.getId(), id, reader, length);

                        session.allocateResultLob(resultLob, null);
                        _resultOut.addLobResult(resultLob);
                    }
                    else
                        throw new InvalidCastException();
                    _parameterValues[i] = clob;
                }
            }
        }

    /**
     * The internal parameter value setter always converts the parameter to
     * the c# type required for data transmission.
     *
     * @param i parameter index
     * @param o object
     * @throws SQLException if either argument is not acceptable.
     */
        private void SetParameter(int i, Object o)
        {
            if (o == null || o == DBNull.Value)
            {
                _parameterValues[i] = null;

                return;
            }

            SessionInterface session = _command.Connection.InnerConnection.SessionProxy;
            SqlType outType = _parameterTypes[i];

            switch (outType.typeCode)
            {
                case Types.OTHER:
                    try
                    {
                        if (o.GetType().IsSerializable)
                        {
                            o = new OtherData(o);
                            break;
                        }
                    }
                    catch (CoreException e)
                    {
                        EfzException.ThrowError(e);
                    }
                    EfzException.ThrowError(Error.error(ErrorCode.X_42565));

                    break;
                case Types.SQL_BIT:
                case Types.SQL_BIT_VARYING:
                    try
                    {
                        if (o is Boolean)
                        {
                            o = outType.convertToDefaultType(session, o);
                            break;
                        }
                        if (o is Int32)
                        {
                            o = outType.convertToDefaultType(session, o);
                            break;
                        }
                        if (o is byte[])
                        {
                            o = outType.convertToDefaultType(session, (object)o);

                            break;
                        }

                        if (o is String)
                        {
                            o = outType.convertToDefaultType(session, o);

                            break;
                        }
                    }
                    catch (CoreException e)
                    {
                        EfzException.ThrowError(e);
                    }
                    EfzException.ThrowError(Error.error(ErrorCode.X_42565));

                    goto case Types.SQL_BINARY;
                // fall through
                case Types.SQL_BINARY:
                case Types.SQL_VARBINARY:
                    if (o is byte[])
                    {
                        o = new BinaryData((byte[])o, !_command.Connection.IsNetConnection);

                        break;
                    }

                    try
                    {
                        if (o is String)
                        {
                            o = outType.convertToDefaultType(session, o);

                            break;
                        }
                    }
                    catch (CoreException e)
                    {
                        EfzException.ThrowError(e);
                    }
                    EfzException.ThrowError(Error.error(ErrorCode.X_42565));

                    break;
                case Types.SQL_BLOB:
                    _parameterValues[i] = o;
                    return;

                case Types.SQL_CLOB:
                    _parameterValues[i] = o;
                    return;

                case Types.SQL_DATE:
                case Types.SQL_TIME_WITH_TIME_ZONE:
                case Types.SQL_TIMESTAMP_WITH_TIME_ZONE:
                case Types.SQL_TIME:
                case Types.SQL_TIMESTAMP:
                    {
                        try
                        {
                            if (o is String)
                            {
                                o = outType.convertToType(session, o,
                                    SqlType.SQL_VARCHAR);

                                break;
                            }
                            o = outType.convertCSharpToSQL(session, o);

                            break;
                        }
                        catch (CoreException e)
                        {
                            EfzException.ThrowError(e);
                        }
                    }
                    goto case Types.TINYINT;
                // fall through
                case Types.TINYINT:
                case Types.SQL_SMALLINT:
                case Types.SQL_INTEGER:
                case Types.SQL_BIGINT:
                case Types.SQL_REAL:
                case Types.SQL_FLOAT:
                case Types.SQL_DOUBLE:
                case Types.SQL_NUMERIC:
                case Types.SQL_DECIMAL:
                    try
                    {
                        if (o is String)
                        {
                            o = outType.convertToType(session, o,
                                    SqlType.SQL_VARCHAR);

                            break;
                        }
                        o = outType.convertToDefaultType(session, o);

                        break;
                    }
                    catch (CoreException e)
                    {
                        EfzException.ThrowError(e);
                    }
                    goto default;
                // fall through
                default:
                    try
                    {
                        o = outType.convertToDefaultType(session, o);

                        break;
                    }
                    catch (CoreException e)
                    {
                        EfzException.ThrowError(e);
                    }
                    break;
            }
            _parameterValues[i] = o;
        }
        


        internal Result PrepareStatement()
        {
            _resultOut = Result.newPrepareStatementRequest();          
            _resultOut.setMainString(_sqlStatement);

            _resultOut.setPrepareOrExecuteProperties(_sqlStatement , 0, 0, 0, queryTimeout,
               resultSetType, resultSetConcurrency, resultSetHoldability,
               _command.FetchGeneratedResults ? ResultConstants.RETURN_GENERATED_KEYS : ResultConstants.RETURN_NO_GENERATED_KEYS
               , null, null);

            Result resultin = _command.Connection.InnerConnection.SessionProxy.execute(_resultOut);

            if (resultin.isError())
            {
                EfzException.ThrowError(resultin);
            }

            RowSetNavigator i;
            i = resultin.getNavigator();

            try
            {
          
                _statementID = resultin.getStatementID();
                
                _rsmdDescriptor = resultin.metaData;
                _isRowCount = resultin.getStatementType() == ResultConstants.UPDATECOUNT;

                
                _pmdDescriptor = resultin.parameterMetaData;
                _parameterTypes = _pmdDescriptor.getParameterTypes();
                _parameterValues = new Object[_parameterTypes.Length];
                _parameterSet = new bool[_parameterTypes.Length];
                _streamLengths = new long[_parameterTypes.Length];
                _parameterModes = _pmdDescriptor.paramModes;
                _parameterNames = _pmdDescriptor.columnLabels;
                
            }
            catch (Exception e)
            {
                throw Error.error(ErrorCode.GENERAL_ERROR, e.ToString());
            }

            _resultOut = Result.newPreparedExecuteRequest(_parameterTypes,
                _statementID);
            _resultOut.setStatement(resultin.getStatement());

            return resultin;
        }

        public void Close()
        {
            if (_statementID != -1)
            {
                try
                {
                    _command.Connection.InnerConnection.SessionProxy.execute(
                                       Result.newFreeStmtRequest(_statementID));
                    _statementID = -1;
                    _rsmdDescriptor = null;
                    _pmdDescriptor = null;
                }
                catch (Exception) { }
            }

            _sqlWarnings = null;
            _generatedResult = null;
            _errorResult = null;
        }

        public void Dispose()
        {
            Close();
        }

        public void ClearWarnings()
        {           
            _sqlWarnings = null;
        }

        public List<EfzException> getWarnings()
        {
            return _sqlWarnings;
        }

        private string BuildStoredProcedureSql(string spName,DbConnection connection)
        {
            StringBuilder builder = new StringBuilder();
            builder.Append("CALL ").Append(spName).Append("( ");

            DataTable dt = connection.GetSchema("PROCEDUREPARAMETERS",new string[]{null,null,spName});

            DataRow row;
            for (int i=0; i< dt.Rows.Count; i++)
            {
                row = dt.Rows[i];
                builder.Append("@").Append(row["COLUMN_NAME"]);
                if (i != dt.Rows.Count - 1)
                    builder.Append(" , ");
            }

            builder.Append(" );");

            return builder.ToString();           
        }
    }
}
